﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.OracleClient;
using System.Web.Configuration;
using TrafficArchives.Common;

namespace TrafficArchives.DataAccess
{
    /// <summary>
    /// 数据库操作基类。
    /// </summary>
    public abstract class DbBase
    {
        /// <summary>
        /// 连接字符串。
        /// </summary>
        protected string ConnectionString
        {
            get { return WebConfigurationManager.ConnectionStrings["db"].ConnectionString; }
        }

        protected OracleConnection Connection()
        {
            return new OracleConnection(ConnectionString);
        }

        /// <summary>
        /// 取得DataTable数据。
        /// </summary>
        /// <param name="sqlText"></param>
        /// <returns></returns>
        protected DataTable ExecuteDataTable(string sqlText)
        {
            using (var conn = Connection())
            {
                conn.Open();
                var dataTable = new DataTable();
                using (var adp = new OracleDataAdapter(sqlText, conn))
                {
                    adp.Fill(dataTable);
                    adp.Dispose();
                }
                conn.Close();
                return dataTable;
            }
        }

        /// <summary>
        /// 取得统计记录数。
        /// </summary>
        /// <param name="sqlText"></param>
        /// <returns></returns>
        protected int ExecuteCount(string sqlText)
        {
            using (var conn = Connection())
            {
                conn.Open();
                int count;
                using (var cmd = new OracleCommand(sqlText, conn))
                {
                    var obj = cmd.ExecuteOracleScalar();
                    count = Int32.Parse(obj.ToString());
                    cmd.Dispose();
                }
                conn.Close();
                return count;
            }
        }

        /// <summary>
        /// 招待无返回的Sql。
        /// </summary>
        /// <param name="sqlText"></param>
        protected void ExecuteNoneQuery(string sqlText)
        {
            using (var conn = Connection())
            {
                conn.Open();
                using (var cmd = new OracleCommand(sqlText, conn))
                {
                    cmd.ExecuteNonQuery();
                }
                conn.Close();
            }
        }

        protected static void PrepareCommand(DbCommand command, DbConnection connection, DbTransaction transaction, CommandType commandType, string commandText, IEnumerable<DbParameter> commandParameters)
        {
            if (command == null)
            {
                throw new ArgumentNullException("command");
            }
            if (string.IsNullOrEmpty(commandText))
            {
                throw new ArgumentNullException("commandText");
            }
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }
            command.Connection = connection;
            command.CommandText = commandText;
            if (transaction != null)
            {
                if (transaction.Connection == null)
                {
                    throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
                }
                command.Transaction = transaction;
            }
            command.CommandType = commandType;
            if (commandParameters != null)
            {
                AttachParameters(command, commandParameters);
            }
        }

        private static void AttachParameters(DbCommand command, IEnumerable<DbParameter> commandParameters)
        {
            if (command == null)
            {
                throw new ArgumentNullException("command");
            }
            if (commandParameters != null)
            {
                foreach (DbParameter parameter in commandParameters)
                {
                    if (parameter != null)
                    {
                        if (((parameter.Direction == ParameterDirection.InputOutput) || (parameter.Direction == ParameterDirection.Input)) && (parameter.Value == null))
                        {
                            parameter.Value = DBNull.Value;
                        }
                        command.Parameters.Add(parameter);
                    }
                }
            }
        }

        protected static DbParameter MakeParam(string paramName, OracleType dbType, int size, string sourceColumn)
        {
            DbParameter parameter = new OracleParameter(paramName, dbType, size, ParameterDirection.Input, sourceColumn, DataRowVersion.Original, false, null);
            return parameter;
        }

        private static DbParameter[] CloneParameters(DbParameter[] originalParameters)
        {
            var parameterArray = new DbParameter[originalParameters.Length];
            int index = 0;
            int length = originalParameters.Length;
            while (index < length)
            {
                parameterArray[index] = (DbParameter)((ICloneable)originalParameters[index]).Clone();
                index++;
            }
            return parameterArray;
        }

        protected int GetPageStart(Pagination p)
        {
            return p.PageIndex*p.PageSize;
        }

        protected int GetPageEnd(Pagination p)
        {
            return (p.PageIndex + 1)*p.PageSize;
        }

        public abstract PaginationData<DataTable> GetData(Request request);

        public abstract void Add(DataTable data);

        public abstract void Update(DataTable data);

        public abstract void Delete(List<string> ids);

        public virtual DataTable GetData(string id)
        {
            return new DataTable();
        }

        public virtual DataTable GetNoneData()
        {
            return null;
        }

        protected void OnAdd(DataTable data, string sqlText, DbParameter[] originalParameters)
        {
            var added = data.GetChanges(DataRowState.Added);
            if (added != null)
            {
                using (var conn = Connection())
                {
                    conn.Open();
                    var trans = conn.BeginTransaction();
                    foreach (DataRow dr in added.Rows)
                    {
                        var parameters = CloneParameters(originalParameters);
                        foreach (var p in parameters)
                        {
                            p.Value = dr[p.ParameterName.Replace(":", "")];
                        }

                        var cmd = new OracleCommand();

                        PrepareCommand(cmd, conn, trans, CommandType.Text, sqlText, parameters);

                        try
                        {
                            cmd.ExecuteNonQuery();
                        }
                        catch (Exception)
                        {
                            trans.Rollback();
                            throw;
                        }

                    }
                    trans.Commit();
                }
            }
        }

        protected void OnUpdate(DataTable data, string sqlText, DbParameter[] originalParameters)
        {
            var modified = data.GetChanges(DataRowState.Modified);
            if (modified != null)
            {
                using (var conn = Connection())
                {
                    conn.Open();
                    var trans = conn.BeginTransaction();
                    foreach (DataRow dr in modified.Rows)
                    {
                        var parameters = CloneParameters(originalParameters);
                        
                        foreach (var p in parameters)
                        {
                            p.Value = dr[p.ParameterName.Replace(":", "")];
                        }
                        var cmd = new OracleCommand();

                        PrepareCommand(cmd, conn, trans, CommandType.Text, sqlText, parameters);

                        try
                        {
                            cmd.ExecuteNonQuery();
                        }
                        catch (Exception)
                        {
                            trans.Rollback();
                            throw;
                        }

                    }
                    trans.Commit();
                }
            }
        }
    }
}